﻿Imports System.Text
Imports CrystalDecisions.CrystalReports.Engine
Public Class CrystlViewer

    '设置参数字段
    Public _ParamHashTable As New Hashtable
    '设置公式字段
    Public _FormutaHashTable As New Hashtable
    '设置数据源
    Public _DataReport As New DataSet
    '参数值
    Public _ParamsValues As New Hashtable

    '设置报表的类型
    Public Enum ReportType
        English  '英语模块
        Japanese '日语模块
        Code     '代码统计模块
        SofeWare '软件模块
        WebSite  '网址模块
        OutMonth  '支出一览表月别
        OutMonth1  '支出一览表月别
        InMonth  '收入一览表月别
        InMonth1  '收入一览表月别
        Song     '歌曲一览表
        Trend    '收入支出趋势
    End Enum

    '报表的类型
    Public _Rt As ReportType
    Public _rd As ReportClass
    Public _进度窗体 As ProgessForm

#Region "设置数据源"
    Private Sub CrystlViewer_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        '设置菜单栏
        SetMenu()
        '设置当前窗体为报表预览窗体
        UTIL.gloabs.CurrentForm = Me
        '设置报表标题
        Me.Text = _ParamsValues.Item("TITLE").ToString()
        '设置数据源
        If Not GetData(_Rt) Then
            If _进度窗体 IsNot Nothing Then
                _进度窗体.Visible = False
                _进度窗体.Close()
            End If
            ShowMessageBox("没有数据")
            Me.Close()
            Exit Sub
        End If
        If _Rt = ReportType.SofeWare Then
            Me.CrystalReportViewer1.ShowGroupTreeButton = True
        End If
        '加载报表
        If _Rt = ReportType.English Then
            If ReportConst.TypeReport = 1 Then
                _rd = New CrystalReportEnglishWord
            ElseIf ReportConst.TypeReport = 2 Then
                _rd = New CrystalReportEnglishWordChinese
            Else
                _rd = New CrystalReportEnglish
            End If
        ElseIf _Rt = ReportType.Code Then
            _rd = New CrystalReportCode
        ElseIf _Rt = ReportType.SofeWare Then
            _rd = New CrystalReportSoftWare
        ElseIf _Rt = ReportType.Japanese Then
            _rd = New CrystalReportJapan
        ElseIf _Rt = ReportType.OutMonth Then
            _rd = New CrystalMoneyOut
        ElseIf _Rt = ReportType.OutMonth1 Then
            _rd = New CrystalMoneyOut1
        ElseIf _Rt = ReportType.InMonth Then
            _rd = New CrystalMoneyIn
        ElseIf _Rt = ReportType.InMonth1 Then
            _rd = New CrystalMoneyIn1
        ElseIf _Rt = ReportType.WebSite Then
            _rd = New CrystalWebSite
        ElseIf _Rt = ReportType.Song Then
            _rd = New CrystalSong1
        ElseIf _Rt = ReportType.Trend Then
            _rd = New CrystalTrend
        End If

        '设置数据源
        _rd.SetDataSource(_DataReport)
        '添加报表
        Me.CrystalReportViewer1.ReportSource = _rd
        '设置公式和参数
        SetParamFormula()
        '缩放比率
        Me.CrystalReportViewer1.Zoom(100)

    End Sub

    '设置报表的参数和公式
    Public Sub SetParamFormula()

        '添加参数
        For Each key As Object In _ParamHashTable.Keys
            _rd.SetParameterValue(key, _ParamHashTable.Item(key))
        Next

        '添加公式
        For Each key As Object In _FormutaHashTable.Keys
            _rd.DataDefinition.FormulaFields().Item(key) = _FormutaHashTable.Item(key)
        Next
    End Sub

    Private Function GetData(ByVal rt As ReportType) As Boolean
        If rt = ReportType.English Then
            Return CreateDataSource(_DataReport)
        ElseIf rt = ReportType.Code Then
            Return CreateDataSourceCode(_DataReport)
        ElseIf rt = ReportType.SofeWare Then
            Return CreateDataSourceSoftWare(_DataReport)
        ElseIf _Rt = ReportType.Japanese Then
            Return CreateDataSourceJapan(_DataReport)
        ElseIf _Rt = ReportType.OutMonth Then
            Return CreateDataSourceOutMonth(_DataReport)
        ElseIf _Rt = ReportType.OutMonth1 Then
            Return CreateDataSourceOutMonth1(_DataReport)
        ElseIf _Rt = ReportType.InMonth Then
            Return CreateDataSourceInMonth(_DataReport)
        ElseIf _Rt = ReportType.InMonth1 Then
            Return CreateDataSourceInMonth1(_DataReport)
        ElseIf _Rt = ReportType.WebSite Then
            Return CreateDataSourceWebSite(_DataReport)
        ElseIf _Rt = ReportType.Song Then
            Return CreateDataSourceSong(_DataReport)
        ElseIf _Rt = ReportType.Trend Then
            Return CreateDataSourceTrend(_DataReport)
        End If
        Return True
    End Function

#Region "创建英语数据源"
    Private Function CreateDataSource(ByRef ds As DataSet) As Boolean
        CreateDataSource = Nothing
        '声明适配器
        Dim oa As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
        '声明表当数据源
        Dim dt As New DataTable("XSCENLISHWORD")
        '连接查询数据
        Dim sb As New StringBuilder

        '新版
        If ReportConst.TypeReport = 0 Then
            sb.AppendLine(" SELECT WORD,CHINESEWORD,Count")
            sb.AppendLine(",id ")
        ElseIf ReportConst.TypeReport = 1 Then
            sb.AppendLine(" SELECT ")
            sb.AppendLine(" WORD ")
            sb.AppendLine(" ,''as CHINESEWORD ")
            sb.AppendLine(",id ")
        ElseIf ReportConst.TypeReport = 2 Then
            sb.AppendLine(" SELECT ")
            sb.AppendLine(" ''as WORD ")
            sb.AppendLine(" , CHINESEWORD ")
            sb.AppendLine(",id ")
        End If
        sb.AppendLine(" from XSCENLISHWORD")
        sb.AppendLine(" WHERE 1=1 ")
        '时间范围
        If ReportConst.EnglishTypeReport = 0 Then
            sb.AppendLine(" AND date([OPTIME])>='" + DateTime.Parse(_ParamsValues.Item("开始时间")).ToString("yyyy-MM-dd") + "'")
            sb.AppendLine(" AND date([OPTIME])<='" + DateTime.Parse(_ParamsValues.Item("结束时间")).ToString("yyyy-MM-dd") + "'")
            '备注
        ElseIf ReportConst.EnglishTypeReport = 2 Then
            Dim strFindLikeSql As String = ""
            If CInt(_ParamsValues.Item("当前英语备注ID")) > 0 Then
                strFindLikeSql = " WHERE MemoID=" & CInt(_ParamsValues.Item("当前英语备注ID"))
            End If
            sb.AppendLine(" AND WORD in(select EnglishWord from EnglishMEMO  " + strFindLikeSql + " )")
            '打印记录
        ElseIf ReportConst.EnglishTypeReport = 3 Then
            sb.AppendLine(" AND ID in( " + CStr(_ParamsValues.Item("编号列表")) + " )")
        End If

        '是否按浏览次数排序
        If ReportConst.EnglishOrderBy = 0 Then
            sb.AppendLine(" order by Count ")
            sb.AppendLine(" , OPTIME desc ")
        ElseIf ReportConst.EnglishOrderBy = 1 Then
            sb.AppendLine(" order by Count desc ")
            sb.AppendLine(" , OPTIME desc ")
        Else
            sb.AppendLine(" order by OPTIME desc ")
        End If

        Dim rs As ADODB.Recordset = Nothing
        rs = ct.GetTable(sb.ToString())
        '填充表
        oa.Fill(dt, rs)
        If dt Is Nothing Then
            ShowMessageBox("数据库错误")
            Return False
        End If

        If dt.Rows.Count = 0 Then
            ShowMessageBox("没有数据")
            Return False
        End If
        rs.Close()
        rs = Nothing

        If ReportConst.EnglishTypeReport = 3 Then
            Dim _dt内容 As DataTable = dt.Clone
            For Each strID As String In CStr(_ParamsValues.Item("编号列表")).Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)
                Dim drs() As DataRow = dt.Select("id=" & strID)
                If drs.Length > 0 Then
                    _dt内容.ImportRow(drs(0))
                End If
            Next

            If _dt内容.Rows.Count > 0 Then
                dt = _dt内容
            End If
        End If

        If CInt(_ParamsValues.Item("RowCount")) > 0 Then
            If CInt(_ParamsValues.Item("RowCount")) < dt.Rows.Count Then
                Dim dt1 As DataTable = dt.Clone
                For i As Integer = 0 To CInt(_ParamsValues.Item("RowCount")) - 1
                    dt1.ImportRow(dt.Rows(i))
                Next
                dt1.TableName = "XSCENLISHWORD"
                ds.Tables.Add(dt1)
            Else
                ds.Tables.Add(dt)
            End If
        Else
            ds.Tables.Add(dt)
        End If

        '添加到DS中    
        ds.Tables.Item(0).TableName = "XSCENLISHWORD"
        Return True
    End Function
#End Region

#Region "创建代码数据源"
    Private Function CreateDataSourceCode(ByRef ds As DataSet) As Boolean
        CreateDataSourceCode = Nothing
        '声明适配器
        Dim oa As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
        '声明表当数据源
        Dim dt As New DataTable("XSCCODE")
        '连接查询数据
        Dim sb As New StringBuilder

        Dim typeIds As New List(Of String)
        Dim strSql As String
        If String.IsNullOrEmpty(_ParamsValues.Item("txtParentName").ToString()) Then
            strSql = "select * from XscBugType Where type=2 and ParentID is null"
        Else
            strSql = "select * from XscBugType Where type=2 and ParentID='" + _ParamsValues.Item("txtParentName").ToString() + "'"
        End If

        Dim rs As ADODB.Recordset = ct.GetTable(strSql)
        Dim lstct As New List(Of String)
        Dim strSql2 As String = ""
        Dim rs1 As ADODB.Recordset = Nothing
        dt.Columns.Add("ID", GetType(System.Int32))
        dt.Columns.Add("LanagerName", GetType(String))
        If rs IsNot Nothing Then
            While Not rs.EOF
                lstct.Clear()
                lstct = ct.GetChildIdAndSelf(rs.Fields("id").Value.ToString(), "2")
                strSql2 = String.Join(",", lstct.ToArray())
                strSql2 = "select count(*) as id ,'" + rs.Fields("Name").Value.ToString() + "' as LanagerName from xsccode where type in(" + strSql2 + ")"
                rs1 = ct.GetTable(strSql2)
                dt.Rows.Add(New String() {Int32.Parse(rs1.Fields("id").Value), rs1.Fields("LanagerName").Value.ToString()})
                rs1.Close()
                rs1 = Nothing
                rs.MoveNext()
            End While
            rs.Close()
            rs = Nothing
        End If
        'A-2010-1-2      

        '填充表
        If dt Is Nothing Then
            ShowMessageBox("数据库错误")
            Return False
        End If

        If dt.Rows.Count = 0 Then
            ShowMessageBox("没有数据")
            Return False
        End If

        '添加到DS中
        dt.DefaultView.Sort = "id"
        dt = dt.DefaultView.ToTable()
        ds.Tables.Add(dt)
        '设置表名不能缺少
        ds.Tables.Item(0).TableName = "XSCCODE"
        Return True
    End Function
#End Region

#Region "创建软件数据源"
    Private Function CreateDataSourceSoftWare(ByRef ds As DataSet) As Boolean
        CreateDataSourceSoftWare = False
        '声明适配器
        '声明表当数据源
        Dim dt As New DataTable("DTSOFTWARE")

        Dim sb As New StringBuilder
        sb.AppendLine(" Select ")
        sb.AppendLine(" ( ")
        sb.AppendLine(" Case  When  XscSoftWareType1.id is null then XscSoftWareType.Name   ")
        sb.AppendLine("       Else  XscSoftWareType1.Name ")
        sb.AppendLine("       End) AS Title1")
        sb.AppendLine(",(")
        sb.AppendLine(" Case   When  XscSoftWareType1.id is null then '' ")
        sb.AppendLine("        Else  SoftWare ")
        sb.AppendLine("        End) AS SoftAareTypeName")
        sb.AppendLine(",Title as Title ")
        sb.AppendLine(" From XscSoftWare")
        sb.AppendLine(" LEFT Join XscSoftWareType as XscSoftWareType")
        sb.AppendLine(" ON XscSoftWare.SoftWare=XscSoftWareType.name")
        sb.AppendLine(" LEFT join XscSoftWareType as XscSoftWareType1")
        sb.AppendLine(" On XscSoftWareType.ParentID=XscSoftWareType1.id")
        sb.AppendLine(" Order by Title1,SoftAareTypeName")
        dt = ct.FillTable(sb.ToString)
        ds.Tables.Add(dt)
        ds.Tables.Item(0).TableName = "DTSOFTWARE"
        CreateDataSourceSoftWare = True
    End Function
#End Region

#Region "创日语建数据源"
    Private Function CreateDataSourceJapan(ByRef ds As DataSet) As Boolean
        CreateDataSourceJapan = Nothing
        '声明适配器
        Dim oa As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
        '声明表当数据源
        Dim dt As New DataTable("DsJapanWord_XSCWORD")
        '连接查询数据
        Dim sb As New StringBuilder

        sb.AppendLine(" SELECT *")

        sb.AppendLine(" from XSCWORD ")
        sb.AppendLine(" WHERE 1=1 ")
        Dim dbtype As Integer = 0
        dbtype = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings("dbtype"))
        If dbtype = 1 Then
            sb.AppendLine(" AND OPTIME>=#" + _ParamsValues.Item("dtStartTime").ToString() + "#")
            sb.AppendLine(" AND OPTIME<=#" + _ParamsValues.Item("dtEndTime").ToString() + "#")
        ElseIf dbtype = 2 Then
            sb.AppendLine(" AND OPTIME>='" + _ParamsValues.Item("dtStartTime").ToString() + "'")
            sb.AppendLine(" AND OPTIME<='" + _ParamsValues.Item("dtEndTime").ToString() + "'")
        ElseIf dbtype = 3 Then
            sb.AppendLine(" AND date([OPTIME])>='" + DateTime.Parse(_ParamsValues.Item("dtStartTime")).ToString("yyyy-MM-dd") + "'")
            sb.AppendLine(" AND date([OPTIME])<='" + DateTime.Parse(_ParamsValues.Item("dtEndTime")).ToString("yyyy-MM-dd") + "'")
        End If
        Dim rs As ADODB.Recordset = ct.GetTable(sb.ToString())
        '填充表
        oa.Fill(dt, rs)
        If dt Is Nothing Then
            ShowMessageBox("数据库错误")
            Return False
        End If

        If dt.Rows.Count = 0 Then
            ShowMessageBox("没有数据")
            Return False
        End If
        rs.Close()
        rs = Nothing

        '添加到DS中
        ds.Tables.Add(dt)
        '设置表名不能缺少
        ds.Tables.Item(0).TableName = "XSCWORD"
        Return True
    End Function
#End Region

#Region "创建支出月别数据源"
    Private Function CreateDataSourceOutMonth(ByRef ds As DataSet) As Boolean
        Dim sb As New System.Text.StringBuilder
        sb.AppendLine(" SELECT WysOutput.*  ")
        sb.AppendLine(" ,XscBugType.Name ")
        sb.AppendLine(" ,WysInputUser.username username1")
        sb.AppendLine(" FROM WysOutput ")
        sb.AppendLine(" LEFT JOIN XscBugType ")
        sb.AppendLine(" ON WysOutput.type=XscBugType.id ")
        sb.AppendLine(" LEFT JOIN WysInputUser ")
        sb.AppendLine(" ON WysOutput.username=WysInputUser.id ")
        sb.AppendLine(" WHERE date([DateTime])>='" + DateTime.Parse(_ParamsValues.Item("开始时间")).ToString("yyyy-MM-dd") + "'")
        sb.AppendLine(" AND date([DateTime])<='" + DateTime.Parse(_ParamsValues.Item("结束时间")).ToString("yyyy-MM-dd") + "'")

        If (_ParamsValues.Contains("用户")) Then
            sb.AppendLine(" AND WysOutput.UserName ='" & _ParamsValues.Item("用户").ToString & "'")
        End If
        sb.AppendLine(" ORDER BY WysOutput.DateTime ")

        Dim dt As DataTable = ct.FillTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return False
        End If

        Dim dt月别 As New DataTable
        dt月别.Columns.Add("内容")
        dt月别.Columns.Add("日期", GetType(Date))
        dt月别.Columns.Add("类型编号")
        dt月别.Columns.Add("类型内容")
        dt月别.Columns.Add("金额", GetType(Decimal))
        dt月别.Columns.Add("年月日")
        dt月别.Columns.Add("年月")
        dt月别.Columns.Add("年")
        dt月别.Columns.Add("备注")
        dt月别.Columns.Add("用户")
        Dim dr月别 As DataRow
        For Each dr As DataRow In dt.Rows
            dr月别 = dt月别.NewRow
            dr月别("内容") = dr("Title")
            dr月别("日期") = dr("DateTime")
            dr月别("类型编号") = dr("TYPE")
            dr月别("类型内容") = dr("NAME")
            dr月别("金额") = dr("COUNT")
            dr月别("年月日") = CDate(dr("DateTime")).ToString("yyyy年MM月dd日")
            dr月别("年月") = CDate(dr("DateTime")).ToString("yyyy年MM月")
            dr月别("年") = CDate(dr("DateTime")).ToString("yyyy年")
            dr月别("备注") = dr("memo")
            dr月别("用户") = dr("username1")
            If (_ParamsValues.Contains("用户")) Then
                dr月别("用户") = ""
            End If
            dt月别.Rows.Add(dr月别)
        Next

        ds.Tables.Add(dt月别)
        ds.Tables.Item(0).TableName = "Money"
        Return True
    End Function
#End Region

#Region "创建支出月别数据源1"
    Private Function CreateDataSourceOutMonth1(ByRef ds As DataSet) As Boolean
        Dim sb As New System.Text.StringBuilder
        sb.AppendLine(" SELECT WysOutput.*  ")
        sb.AppendLine(" ,XscBugType.Name ")
        sb.AppendLine(" ,WysInputUser.username username1")
        '(WORD||'('||Count)||'次)'

        sb.AppendLine(" FROM WysOutput ")
        sb.AppendLine(" LEFT JOIN XscBugType ")
        sb.AppendLine(" ON WysOutput.type=XscBugType.id ")
        sb.AppendLine(" LEFT JOIN WysInputUser ")
        sb.AppendLine(" ON WysOutput.username=WysInputUser.id ")
        sb.AppendLine(" WHERE date([DateTime])>='" + DateTime.Parse(_ParamsValues.Item("开始时间")).ToString("yyyy-MM-dd") + "'")
        sb.AppendLine(" AND date([DateTime])<='" + DateTime.Parse(_ParamsValues.Item("结束时间")).ToString("yyyy-MM-dd") + "'")

        If (_ParamsValues.Contains("用户")) Then
            sb.AppendLine(" AND WysOutput.UserName ='" & _ParamsValues.Item("用户").ToString & "'")
        End If
        sb.AppendLine(" ORDER BY WysOutput.DateTime ")

        Dim dt As DataTable = ct.FillTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return False
        End If

        Dim dt月别 As New DataTable
        dt月别.Columns.Add("内容")
        dt月别.Columns.Add("日期", GetType(Date))
        dt月别.Columns.Add("类型编号")
        dt月别.Columns.Add("类型内容")
        dt月别.Columns.Add("金额", GetType(Decimal))
        dt月别.Columns.Add("年月日")
        dt月别.Columns.Add("年月日1")
        dt月别.Columns.Add("年月")
        dt月别.Columns.Add("年")
        dt月别.Columns.Add("备注")
        dt月别.Columns.Add("用户")
        dt月别.Columns.Add("用户1")
        dt月别.Columns.Add("分类别")
        dt月别.Columns.Add("分类别1")
        Dim dr月别 As DataRow
        Dim 分类1 As Boolean = CBool(_ParamsValues.Item("分类1"))
        Dim 分类2 As Boolean = CBool(_ParamsValues.Item("分类2"))
        Dim 分类3 As Boolean = CBool(_ParamsValues.Item("分类3"))
        If Not 分类1 AndAlso Not 分类2 AndAlso Not 分类3 Then
            分类1 = True
        End If

        For Each dr As DataRow In dt.Rows
            dr月别 = dt月别.NewRow
            dr月别("内容") = dr("Title")
            dr月别("日期") = dr("DateTime")
            dr月别("类型编号") = dr("TYPE")
            dr月别("类型内容") = dr("NAME")
            dr月别("金额") = dr("COUNT")
            dr月别("年月日") = CDate(dr("DateTime")).ToString("yyyyMMdd")
            dr月别("年月日1") = CDate(dr("DateTime")).ToString("yyyy年MM月dd日")
            dr月别("年月") = CDate(dr("DateTime")).ToString("yyyyMM")
            dr月别("年") = CDate(dr("DateTime")).ToString("yyyy")
            dr月别("备注") = dr("memo")
            dr月别("用户") = dr("username1")
            dr月别("用户1") = dr("username")


            dt月别.Rows.Add(dr月别)
            If 分类1 AndAlso 分类2 AndAlso 分类3 Then
                dr月别("分类别") = dr月别("用户") & "/" & dr月别("年月日1") & dr月别("类型内容")
                dr月别("分类别1") = dr月别("用户1") & "/" & dr月别("年月日") & dr月别("类型编号")
            ElseIf 分类1 AndAlso 分类2 Then
                dr月别("分类别") = dr月别("用户") & "/" & dr月别("年月日1")
                dr月别("分类别1") = dr月别("用户1") & "/" & dr月别("年月日")
            ElseIf 分类2 AndAlso 分类3 Then
                dr月别("分类别") = dr月别("年月日1") & "/" & dr月别("类型内容")
                dr月别("分类别1") = dr月别("年月日") & "/" & dr月别("类型编号")
            ElseIf 分类1 AndAlso 分类3 Then
                dr月别("分类别") = dr月别("用户") & "/" & dr月别("类型内容")
                dr月别("分类别1") = dr月别("用户1") & "/" & dr月别("类型编号")
            ElseIf 分类1 Then
                dr月别("分类别") = dr月别("用户")
                dr月别("分类别1") = dr月别("用户1")
            ElseIf 分类2 Then
                dr月别("分类别") = dr月别("年月日1")
                dr月别("分类别1") = dr月别("年月日")
            ElseIf 分类3 Then
                dr月别("分类别") = dr月别("类型内容")
                dr月别("分类别1") = dr月别("类型编号")
            End If
        Next
        '处理分类
        Dim dv As DataView = dt月别.DefaultView
        dv.Sort = "分类别1"
        dt月别 = dv.ToTable()
        ds.Tables.Add(dt月别)
        ds.Tables.Item(0).TableName = "Money"
        Return True
    End Function
#End Region

#Region "创建收入月别数据源"
    Private Function CreateDataSourceInMonth(ByRef ds As DataSet) As Boolean
        Dim sb As New System.Text.StringBuilder
        sb.AppendLine(" SELECT WysInput.*  ")
        sb.AppendLine(" ,XscBugType.Name ")
        sb.AppendLine(" ,WysInputUser.username username1")
        sb.AppendLine(" FROM WysInput ")
        sb.AppendLine(" LEFT JOIN XscBugType ")
        sb.AppendLine(" ON WysInput.type=XscBugType.id ")
        sb.AppendLine(" LEFT JOIN WysInputUser ")
        sb.AppendLine(" ON WysInput.username=WysInputUser.id ")
        sb.AppendLine(" WHERE date([DateTime])>='" + DateTime.Parse(_ParamsValues.Item("开始时间")).ToString("yyyy-MM-dd") + "'")
        sb.AppendLine(" AND date([DateTime])<='" + DateTime.Parse(_ParamsValues.Item("结束时间")).ToString("yyyy-MM-dd") + "'")
        If (_ParamsValues.Contains("用户")) Then
            sb.AppendLine(" AND WysInput.UserName ='" & _ParamsValues.Item("用户").ToString & "'")
        End If
        sb.AppendLine(" ORDER BY WysInput.DateTime ")

        Dim dt As DataTable = ct.FillTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return False
        End If

        Dim dt月别 As New DataTable
        dt月别.Columns.Add("内容")
        dt月别.Columns.Add("日期", GetType(Date))
        dt月别.Columns.Add("类型编号")
        dt月别.Columns.Add("类型内容")
        dt月别.Columns.Add("金额", GetType(Decimal))
        dt月别.Columns.Add("年月日")
        dt月别.Columns.Add("年月")
        dt月别.Columns.Add("年")
        dt月别.Columns.Add("备注")
        dt月别.Columns.Add("用户")
        Dim dr月别 As DataRow
        For Each dr As DataRow In dt.Rows
            dr月别 = dt月别.NewRow
            dr月别("内容") = dr("Title")
            dr月别("日期") = dr("DateTime")
            dr月别("类型编号") = dr("TYPE")
            dr月别("类型内容") = dr("NAME")
            dr月别("金额") = dr("COUNT")
            dr月别("年月日") = CDate(dr("DateTime")).ToString("yyyyMMdd")
            dr月别("年月") = CDate(dr("DateTime")).ToString("yyyyMM")
            dr月别("年") = CDate(dr("DateTime")).ToString("yyyy")
            dr月别("备注") = dr("memo")
            dr月别("用户") = dr("username1")
            If (_ParamsValues.Contains("用户")) Then
                dr月别("用户") = ""
            End If
            dt月别.Rows.Add(dr月别)
        Next

        ds.Tables.Add(dt月别)
        ds.Tables.Item(0).TableName = "Money"
        Return True
    End Function
#End Region

#Region "创建收入月别数据源1"
    Private Function CreateDataSourceInMonth1(ByRef ds As DataSet) As Boolean
        Dim sb As New System.Text.StringBuilder
        sb.AppendLine(" SELECT WysInput.*  ")
        sb.AppendLine(" ,XscBugType.Name ")
        sb.AppendLine(" ,WysInputUser.username username1")
        sb.AppendLine(" FROM WysInput ")
        sb.AppendLine(" LEFT JOIN XscBugType ")
        sb.AppendLine(" ON WysInput.type=XscBugType.id ")
        sb.AppendLine(" LEFT JOIN WysInputUser ")
        sb.AppendLine(" ON WysInput.username=WysInputUser.id ")
        sb.AppendLine(" WHERE date([DateTime])>='" + DateTime.Parse(_ParamsValues.Item("开始时间")).ToString("yyyy-MM-dd") + "'")
        sb.AppendLine(" AND date([DateTime])<='" + DateTime.Parse(_ParamsValues.Item("结束时间")).ToString("yyyy-MM-dd") + "'")
        If (_ParamsValues.Contains("用户")) Then
            sb.AppendLine(" AND WysInput.UserName ='" & _ParamsValues.Item("用户").ToString & "'")
        End If
        sb.AppendLine(" ORDER BY WysInput.DateTime ")

        Dim dt As DataTable = ct.FillTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return False
        End If

        Dim dt月别 As New DataTable
        dt月别.Columns.Add("内容")
        dt月别.Columns.Add("日期", GetType(Date))
        dt月别.Columns.Add("类型编号")
        dt月别.Columns.Add("类型内容")
        dt月别.Columns.Add("金额", GetType(Decimal))
        dt月别.Columns.Add("年月日")
        dt月别.Columns.Add("年月")
        dt月别.Columns.Add("年")
        dt月别.Columns.Add("备注")
        dt月别.Columns.Add("用户")
        Dim dr月别 As DataRow
        For Each dr As DataRow In dt.Rows
            dr月别 = dt月别.NewRow
            dr月别("内容") = dr("Title")
            dr月别("日期") = dr("DateTime")
            dr月别("类型编号") = dr("TYPE")
            dr月别("类型内容") = dr("NAME")
            dr月别("金额") = dr("COUNT")
            dr月别("年月日") = CDate(dr("DateTime")).ToString("yyyyMMdd")
            dr月别("年月") = CDate(dr("DateTime")).ToString("yyyyMM")
            dr月别("年") = CDate(dr("DateTime")).ToString("yyyy")
            dr月别("备注") = dr("memo")
            dr月别("用户") = dr("username1")
            If (_ParamsValues.Contains("用户")) Then
                dr月别("用户") = ""
            End If
            dt月别.Rows.Add(dr月别)
        Next

        ds.Tables.Add(dt月别)
        ds.Tables.Item(0).TableName = "Money"
        Return True
    End Function
#End Region

#Region "创建网址数据源"
    Private Function CreateDataSourceWebSite(ByRef ds As DataSet) As Boolean
        Dim sb As New System.Text.StringBuilder
        sb.AppendLine(" SELECT   ")
        sb.AppendLine(" (")
        sb.AppendLine("  Case   ")
        sb.AppendLine("     When XscBugType1.id is null Then  XscBugType.NAME ")
        sb.AppendLine("     Else  XscBugType1.NAME ")
        sb.AppendLine("  End ")
        sb.AppendLine("  ) as 类型1  ")
        sb.AppendLine(" ,(")
        sb.AppendLine(" Case when XscBugType1.id is null Then ''")
        sb.AppendLine(" Else XscBugType.NAME  ")
        sb.AppendLine(" End)  as 类型 ")
        sb.AppendLine(" ,XscWebSite.TITLE as 说明 ")
        sb.AppendLine(" ,XscWebSite.WebSiteUrl as 网址 ")
        sb.AppendLine(" FROM XscWebSite ")
        sb.AppendLine(" LEFT JOIN XscBugType  as XscBugType")
        sb.AppendLine(" ON XscWebSite.type=XscBugType.id  ")
        sb.AppendLine(" LEFT JOIN XscBugType as XscBugType1")
        sb.AppendLine(" ON XscBugType.ParentID=XscBugType1.id ")
        sb.AppendLine(" ORDER BY XscBugType.NAME ")

        Dim dt As DataTable = ct.FillTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return False
        End If

        ds.Tables.Add(dt)
        ds.Tables.Item(0).TableName = "WebSite"
        Return True
    End Function
#End Region

#Region "创建歌曲数据源"
    Private Function CreateDataSourceSong(ByRef ds As DataSet) As Boolean
        Dim sb As New System.Text.StringBuilder
        sb.AppendLine(" SELECT songer as 类型名称  ")
        sb.AppendLine(" ,XSCSong.song as 说明 ")
        sb.AppendLine(" ,XSCSong.memo as 网址 ")
        sb.AppendLine(" FROM XSCSong ")

        Dim dt As DataTable = ct.FillTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return False
        End If

        ds.Tables.Add(dt)
        ds.Tables.Item(0).TableName = "WebSite"
        Return True
    End Function
#End Region

#Region "创建趋势数据源"
    Private Function CreateDataSourceTrend(ByRef ds As DataSet) As Boolean
        Dim sb As New System.Text.StringBuilder
        Dim dt As New DataTable
        '收入
        If ReportConst.TypeTrend1 = 0 Then
            If ReportConst.TypeTrend = 0 Then '单月

            ElseIf ReportConst.TypeTrend = 1 Then '月范围

            ElseIf ReportConst.TypeTrend = 2 Then '单年

            ElseIf ReportConst.TypeTrend = 3 Then '年范围

            End If
        Else '支出
            If ReportConst.TypeTrend = 0 Then '单月
                sb.Length = 0
                Dim dc As DataColumn
                dt.Columns.Add("年月日")
                dc = dt.Columns.Add("金额", GetType(Decimal))
                dt.Columns.Add("用户")
                dc.DefaultValue = 0

                '抽数据
                Dim Year As Integer = DateTime.Parse(_ParamsValues.Item("开始时间")).Year
                Dim endmouth As Integer = DateTime.Parse(_ParamsValues.Item("开始时间")).Month

                Dim strSql As String = String.Empty
                Dim strSql1 As String = String.Empty
                Dim dr As DataRow

                For day As Integer = 1 To DateTime.DaysInMonth(Year, endmouth)
                    Dim strBegin As String = Year.ToString("0000") + "-" + endmouth.ToString("00") + "-" + day.ToString("00")
                    strSql1 = "select  sum(count) as count from WysOutput where date([DateTime])='" + strBegin + "'"

                    dr = dt.NewRow
                    dr(0) = day.ToString & "日"

                    Dim dt2 As DataTable = ct.FillTable(strSql1)
                    If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                        dr(1) = 结果(dt2.Rows(0)(0))
                    Else
                        dr(1) = 0
                    End If

                    dt.Rows.Add(dr)
                Next
            ElseIf ReportConst.TypeTrend = 1 Then '月范围

            ElseIf ReportConst.TypeTrend = 2 Then '单年

            ElseIf ReportConst.TypeTrend = 3 Then '年范围

            End If
        End If

        If dt.Rows.Count = 0 Then
            Return False
        End If

        ds.Tables.Add(dt)
        ds.Tables.Item(0).TableName = "Trend"


        Return True
    End Function

    Public Function 结果(ByVal ob As Object) As Decimal
        If ob Is Nothing Then
            Return 0
        End If
        If ob Is DBNull.Value Then
            ob = 0
        End If
        Return CDec(ob)
    End Function

#End Region
#End Region

#Region "追加查找窗体 A-2010-05-28"
    Dim itemMenu As System.Windows.Forms.ToolStripMenuItem
    Public Sub SetMenu()
        If itemMenu Is Nothing Then
            If _Rt = ReportType.English Then
                If ReportConst.TypeReport <> 1 OrElse _
                ReportConst.TypeReport <> 2 Then
                    itemMenu = Me.ContextMenuStrip1.Items.Add("保存数据")
                    AddHandler itemMenu.Click, AddressOf 查找窗口ToolStripMenuItem_Click
                End If

            End If

        End If
    End Sub

    Private Sub 查找窗口ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            Dim bFirst As Boolean = False
            Dim sb As New StringBuilder
            'For Each dr As DataRow In _DataReport.Tables(0).Rows
            '    If bFirst = False Then
            '        sb.Append("'" & CStr(dr("id")) & "'")
            '        bFirst = True
            '    Else
            '        sb.Append("," & "'" & CStr(dr("id")) & "'")
            '    End If
            'Next
            For i As Int32 = 0 To _DataReport.Tables(0).Rows.Count - 1
                If bFirst = False Then
                    sb.Append("'" & CStr(_DataReport.Tables(0).Rows(i)("id")) & "'")
                    bFirst = True
                Else
                    sb.Append("," & "'" & CStr(_DataReport.Tables(0).Rows(i)("id")) & "'")
                End If
            Next
            Dim strSql As String = "insert into PrintLog([type],[DateTime],[KEYS],id) values(0,?,?,?)"
            Dim cmd As New ADODB.Command
            cmd.CommandText = strSql
            Dim pa As ADODB.Parameter = cmd.CreateParameter(, ADODB.DataTypeEnum.adDBTimeStamp, ADODB.ParameterDirectionEnum.adParamInput, 100, Now.ToString())
            cmd.Parameters.Append(pa)

            pa = cmd.CreateParameter("KEYS", ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, sb.ToString().Length, sb.ToString())
            cmd.Parameters.Append(pa)

            pa = cmd.CreateParameter(, ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 36, System.Guid.NewGuid.ToString("D"))
            cmd.Parameters.Append(pa)

            If (ct.ExcCommand(cmd)) Then
                ShowMessageBox("保存成功")
            End If
        Catch ex As Exception

        End Try
    End Sub


#End Region
End Class
